package com.zhucai.credit.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.zhucai.credit.model.EntCrProdEntity;

/**
 * 
 * @author hsg
 * 筑保通信息的Repository
 */
@Repository
public interface EntCrProdEntityRepository extends
		PagingAndSortingRepository<EntCrProdEntity, Long>,
		JpaSpecificationExecutor<EntCrProdEntity> {

	EntCrProdEntity findByEnAccIdAndStatusNotAndCrPrTypeId(Long userid, int delete, Long bankId);
	/**
	 * 
	 * @date 2017年11月30日 @user hsg
	 */
	EntCrProdEntity findByStatusNotAndOrgnNumAndCrPrTypeId(int delete, String orgn_num,Long crProdId);
	/**
	 * 根据企业id查询筑保通信息
	 * @param delete
	 * @param enterpriseId
	 * @param string
	 * @return
	 */
	@Query(value="select * from finance_ent_cr_prod t where t.status<>?1 and t.enterprise_id = ?2 and t.ent_type = ?3 and t.cr_pr_type_id = ?4",nativeQuery=true)
	EntCrProdEntity findCrProdInfo(int delete, Long enterpriseId, String entType,Long bankId);
	/**
	 * 根据商家类型和组织机构代码查询筑保通信息
	 * @param delete
	 * @param orgn_num
	 * @param string
	 * @return
	 */
	@Query(value="select * from finance_ent_cr_prod t where t.status<>?1 and t.orgn_num = ?2 and t.ent_type = ?3 and cr_pr_type_id = ?4",nativeQuery=true)
	EntCrProdEntity findByStatusNotAndOrgnNumAndEntType(int delete, String orgn_num, String entType,Long crProdId);
	
	/**
	 * 根据金融id查询服务费率
	 * @param participator
	 * @param entType 
	 * @param crpordId 
	 * @return
	 */
	@Query(value = "select * from finance_ent_cr_prod t where t.en_acc_id = ?1 and cr_pr_type_id = ?2 and t.ent_type = ?3 and t.zhucai_prod_type = ?4",nativeQuery = true)
	EntCrProdEntity findByEnAccId(Long participator, Long crpordId, String entType,String zhubaotong);
	/**
	 * 查询此金融产品是否开通
	 * @param tid
	 * @param delete
	 * @param tid2
	 * @param string
	 * @return
	 */
	@Query(value = "select * from finance_ent_cr_prod t where t.en_acc_id = ?1 and status <> ?2 and cr_pr_type_id = ?3 and t.zhucai_prod_type = ?4 and t.ecp_status = 0 and en_cr_status = ?5",nativeQuery = true)
	EntCrProdEntity findZhucaiProdRecord(Long tid, int delete, Long cr_pr_type_id,
			String zhucaiType,String crProdStatus);
	
	/**
	 * 查询筑保通列表
	 * @version 2018年8月6日
	 * @param tid
	 * @param delete
	 * @return
	 */
	@Query(value = "select * from finance_ent_cr_prod t where t.en_acc_id = ?1 and status <> ?2 and t.zhucai_prod_type = ?3 and t.ecp_status = 0 and t.en_cr_status = ?4",nativeQuery = true)
	List<EntCrProdEntity> findByAccIdList(Long tid, int delete,String zhuProdType,String en_cr_status);
	
	/**
	 * 修改联系人信息
	 * @version 2018年8月8日
	 * @param tid
	 * @param linkPerson
	 * @param linkTel
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_ent_cr_prod set link_person = ?2,link_tel = ?3 where id =?1",nativeQuery=true)
	Integer updatePersonInfo(Long tid, String linkPerson, String linkTel);
	
	/**
	 * 根据组织机构代码更新筑保通状态
	 * @version 2018年8月8日
	 * @param tid
	 * @param linkPerson
	 * @param linkTel
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_ent_cr_prod set en_cr_status = 'PASS' where orgn_num =?1 and cr_pr_type_id = ?2",nativeQuery=true)
	Integer updatePassByOrgnNum(String OrgnNum, Long crProdId);
	/**
	 * 根据id更新筑保通状态
	 * @version 2018年8月8日
	 * @param tid
	 * @param linkPerson
	 * @param linkTel
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_ent_cr_prod set en_cr_status = 'PASS' where id=?1",nativeQuery=true)
	Integer updatePassById(Long id);
	/**
	 * 
	 * @version 2018年9月22日
	 * @param enterpriseId
	 * @param entType
	 * @return
	 */
	@Query(value="select * from finance_ent_cr_prod where enterprise_id=?1 and ent_type=?2 and cr_pr_type_id = ?3 and zhucai_prod_type=?4",nativeQuery=true)
	EntCrProdEntity findByEntId(Long enterpriseId, String entType,Long crProdId,String zhucai_prod_type);
	



}
